import { VTrainerCourse ,ScensCount} from '../../types/vTrainerCourse'
import { select } from '../../utils/db'
import { Page, PageReq } from '../../types/page'
import { TrainerCourseDao } from '../dao/trainerCourseDao'
export class VTrainerCourseDao {
  /**
   * 获取视图详情
   */
  static async getById(id: number): Promise<VTrainerCourse> {
    const result = await select<VTrainerCourse>(
      'SELECT * FROM v_trainer_course WHERE id = ? LIMIT 1',
      [id]
    )
    return result[0]
  }

  /**
   * 获取今日训练患者总数
   */
  static async getTodayTrainerTotal(course_date: string): Promise<number> {
    const result = await select<VTrainerCourse[]>(
      'SELECT DISTINCT course_id FROM v_trainer_course WHERE course_date = ? order by course_id',
      [course_date]
    )
    if(result && result.length>0){
      let total = 0;
      for(const item of result){
        const num = await TrainerCourseDao.countTrainerByCourseId(item.course_id);
        total = total + num
      }
      return total
    }else{
      return 0
    }
  }
  

  /**
   * 训练场景次数前5名排行
   */
  static async getScensTotal(): Promise<ScensCount[]> {
    const result = await select<ScensCount[]>(
      'SELECT secens_id, scens_name,COUNT(*) as total FROM v_trainer_course GROUP BY secens_id ,scens_name ORDER BY total DESC LIMIT 5'
    )
    return result
  }

  /**
   * 分页查询视图列表
   */
  static async page(params: PageReq<Partial<VTrainerCourse>>): Promise<Page<VTrainerCourse>> {
    const { pageNum, pageSize } = params
    const offset = (pageNum - 1) * pageSize
    const fields = []
    const values = []
    const queryParams = params.params
    
    if (queryParams.course_id) {
      fields.push('course_id = ?')
      values.push(queryParams.course_id)
    }
    if (queryParams.train_doctor) {
      fields.push('train_doctor LIKE ?')
      values.push(`%${queryParams.train_doctor}%`)
    }
    if (queryParams.course_time_id) {
      fields.push('course_time_id = ?')
      values.push(queryParams.course_time_id)
    }
    if (queryParams.course_time_name) {
      fields.push('course_time_name LIKE ?')
      values.push(`%${queryParams.course_time_name}%`)
    }
    if (queryParams.scens_name) {
      fields.push('scens_name LIKE ?')
      values.push(`%${queryParams.scens_name}%`)
    }

    const condition = fields.length > 0 ? `WHERE ${fields.join(' AND ')}` : ''
    const [totalResult, listResult] = await Promise.all([
      select('SELECT COUNT(*) as count FROM v_trainer_course ' + condition, values),
      select(`SELECT * FROM v_trainer_course ${condition} LIMIT ${pageSize} OFFSET ${offset}`, values)
    ])

    return {
      total: totalResult[0].count || 0,
      list: (listResult as unknown as VTrainerCourse[]) || []
    }
  }
}